﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using DBSchemaService.Utils;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Server;
using Microsoft.TeamFoundation.VersionControl.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System.IO;
using System.Configuration;

namespace DBSchemaService.Core
{
    /// <summary>
    /// This will create the sprocs.sql file that makes up all the separate Stored Procedures created for each DB.
    /// </summary>
    class StoredProcedureProcessor
    {
        internal readonly List<StoredProcedureFileConfiguration> storedProcConfigurations = ConfigurationHelper.GetStoredProcedureFileConfigurations();
        internal VersionControlServer _versionControlServer;
        internal StringBuilder dropProcScript = new StringBuilder();
        internal readonly string _emailMeWhenSuccessful = ConfigurationManager.AppSettings["groupEmail"];
        internal readonly string _emailMeWhenSuccessfulName = ConfigurationManager.AppSettings["groupName"];
        internal readonly string _workspace = ConfigurationManager.AppSettings["workspace"];
        internal readonly string _additionalScript = ConfigurationManager.AppSettings["additionalScript"];

        public StringBuilder GetInitialScript(TeamFoundationServer tfs)
        {
            StringBuilder dropProcScript = new StringBuilder();

            //listen for script changes 

            //add a listener
            _versionControlServer = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            _versionControlServer.CommitCheckin += new CommitCheckinEventHandler(_versionControlServer_CommitCheckin);

            //check if the checkin was for sql script in the folders



            return dropProcScript;
        }

        void _versionControlServer_CommitCheckin(object sender, CommitCheckinEventArgs e)
        {
            //here we want to create the drop scripts here


            throw new NotImplementedException();
        }



        /// <summary>
        /// Go To TFS, get the latest, read each file in given directory
        /// </summary>
        public bool Start(TeamFoundationServer tfs, bool IsFileOutputAppend, List<EmailInfo> email, bool isScriptNew)
        {
            VersionControlServer sourceControl;
            string serverPath = string.Empty, localPath = string.Empty, sprocsRootLocalPath = string.Empty, sprocsRootServerPath = string.Empty;
            StringBuilder sprocFileBuilder = new StringBuilder();
            Workspace workspace = null;
            Workspace[] workspaces;
            string line = string.Empty;
            List<DirectoryInfo> subdirInfoList = new List<DirectoryInfo>();
            int changeSetNumber = 0;
            string database = string.Empty;
            string databaseForEmail = string.Empty;
            //create a workspace instance to modify schema.sql from tfs
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());
            string setCountOn = String.Format("SET NOCOUNT OFF{0}GO{0}",Environment.NewLine);
            string setCountOff = String.Format("SET NOCOUNT ON{0}GO{0}", Environment.NewLine);

            if (workspaces.Length == 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
            {
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();
            }
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
            {
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            }

            //get the local path and server path for the work item schema
            foreach (StoredProcedureFileConfiguration storedProcConfig in storedProcConfigurations)
            {
                sprocFileBuilder = new StringBuilder();
                localPath = storedProcConfig.LocalPath.Value;
                serverPath = storedProcConfig.TFSServerPath.Value;
                sprocsRootLocalPath = storedProcConfig.SprocsRootLocalPath;
                sprocsRootServerPath = storedProcConfig.SprocsRootServerPath;
                database = storedProcConfig.Metadata;
                databaseForEmail = storedProcConfig.Database;
                //check if the server path is mapped to a local path
                if (!workspace.IsServerPathMapped(serverPath))
                {
                    workspace.Map(serverPath, localPath);
                }

                if (!workspace.IsServerPathMapped(sprocsRootServerPath))
                {
                    workspace.Map(sprocsRootServerPath, sprocsRootLocalPath);
                }

                //get the latest sprocs file and get all the sprocs
                GetStatus status = workspace.Get();
                Conflict[] conflicts = workspace.QueryConflicts(new string[] { }, false);
                List<FileInfo> filesToProcess = new List<FileInfo>();
                foreach (Conflict conflict in conflicts)
                {
                    conflict.Resolution = Resolution.OverwriteLocal;
                    workspace.ResolveConflict(conflict);
                }

                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(sprocsRootLocalPath);

                sprocFileBuilder.AppendLine(setCountOff);
                ProcessViewsDirectoryTree(sprocFileBuilder, ref line, dir);
                ProcessDirectoryTree(sprocFileBuilder, ref line, dir);
                sprocFileBuilder.AppendLine(setCountOn);

                //check out the file and append the sql script from the streamreader to sprocs.sql
                int numFilesCheckedOut = workspace.PendEdit(localPath);
                workspace.SetLock(localPath, LockLevel.CheckOut);
                using (StreamWriter sw = new StreamWriter(localPath, IsFileOutputAppend))
                {
                    sw.Write(sprocFileBuilder.ToString());
                    if (database.ToLower().Trim() == "test")
                        sw.Write(_additionalScript.ToString());
                }

                //get pending change and check it in to TFS
                PendingChange[] changes = workspace.GetPendingChanges((new string[] { localPath }));

                //check-in the newly created all-in-one sprocs file to TFS
                //only check in the file if it has passed the test!

                if (changes.Length > 0)
                {
                    changeSetNumber = workspace.CheckIn(changes, "Modified by Schema Change Service. " + Environment.NewLine + " ***NO_CI***");
                    //now run the file on the database so env has latest sprocs
                    DatabaseHelper.ApplyDBChanges(sprocFileBuilder.ToString(), databaseForEmail);
                }
                //workspace.Delete();

                //group email               
                if (changeSetNumber > 0)
                {
                    email.Add(new EmailInfo(_emailMeWhenSuccessful, _emailMeWhenSuccessfulName, databaseForEmail, serverPath, changeSetNumber));
                    isScriptNew = true;
                }
                //DatabaseHelper.ApplyDBChanges(sprocFileBuilder.ToString(), databaseForEmail);

                workspace.SetLock(localPath, LockLevel.CheckOut);
            }

            //now instantiate the email processor to generate the email to send out
            EmailProcessor emailProc = new EmailProcessor(email);
            emailProc.Start();
            return isScriptNew;
        }

        private static void ProcessDirectory(StringBuilder sprocFileBuilder, ref string line, ref List<FileInfo> filesToProcess, List<DirectoryInfo> directories)
        {
            //Process, return the stringbuilder object
            foreach (DirectoryInfo dirInfo in directories)
            {
                filesToProcess = dirInfo.GetFiles().Where(a => a.Extension.Equals(".sql")).ToList();
                foreach (FileInfo f in filesToProcess)
                {
                    using (StreamReader reader = new StreamReader(f.FullName))
                    {
                        while ((line = reader.ReadLine()) != null)
                        {
                            sprocFileBuilder.AppendLine(line);
                        }
                    }
                }
            }
        }


        private static void ProcessViewsDirectoryTree(StringBuilder sprocFileBuilder, ref string line, DirectoryInfo root)
        {
            List<DirectoryInfo> subDirs = null;
            List<FileInfo> filesToProcess = null;
            StringBuilder singleScriptBuilder = new StringBuilder();
            //skip files that have Structure.sql

            filesToProcess = root.GetFiles().Where(a => a.Extension.Equals(".sql") && a.FullName.Contains("Views")).ToList();
            foreach (FileInfo f in filesToProcess)
            {
                //TODO: Verify that the script is correct, email user who last checked in file if script is bad

                //if(DatabaseHelper.ApplyDBChanges(singleScriptBuilder


                //Add to the stringbuilder for checking in
                using (StreamReader reader = new StreamReader(f.FullName))
                {
                    while ((line = reader.ReadLine()) != null)
                    {

                        sprocFileBuilder.AppendLine(DBSchemaManager.Core.Shared.CleanString(line));
                    }
                }
            }

            // Now find all the subdirectories under this directory.
            subDirs = root.GetDirectories().ToList();

            foreach (System.IO.DirectoryInfo dirInfo in subDirs)
            {
                // Resursive call for each subdirectory.
                ProcessViewsDirectoryTree(sprocFileBuilder, ref line, dirInfo);
            }
        }

        private static void ProcessDirectoryTree(StringBuilder sprocFileBuilder, ref string line, DirectoryInfo root)
        {
            List<DirectoryInfo> subDirs = null;
            List<FileInfo> filesToProcess = null;
            StringBuilder singleScriptBuilder = new StringBuilder();
            //skip files that have Structure.sql

            filesToProcess = root.GetFiles().Where(a => a.Extension.Equals(".sql") && a.FullName.Contains("SP")).ToList();
            foreach (FileInfo f in filesToProcess)
            {
                //TODO: Verify that the script is correct, email user who last checked in file if script is bad

                //if(DatabaseHelper.ApplyDBChanges(singleScriptBuilder


                //Add to the stringbuilder for checking in
                using (StreamReader reader = new StreamReader(f.FullName))
                {
                    while ((line = reader.ReadLine()) != null)
                    {
                        sprocFileBuilder.AppendLine(DBSchemaManager.Core.Shared.CleanString(line));
                    }
                }
            }

            // Now find all the subdirectories under this directory.
            subDirs = root.GetDirectories().ToList();

            foreach (System.IO.DirectoryInfo dirInfo in subDirs)
            {
                // Resursive call for each subdirectory.
                ProcessDirectoryTree(sprocFileBuilder, ref line, dirInfo);
            }
        }



        public StringBuilder GetStoredProcScript(TeamFoundationServer tfs)
        {
            VersionControlServer sourceControl;
            string serverPath = string.Empty, localPath = string.Empty, sprocsRootLocalPath = string.Empty, sprocsRootServerPath = string.Empty;
            StringBuilder sprocFileBuilder = new StringBuilder();
            Workspace workspace = null;
            Workspace[] workspaces;
            string line = string.Empty;
            List<DirectoryInfo> subdirInfoList = new List<DirectoryInfo>();

            //create a workspace instance to modify schema.sql from tfs
            sourceControl = (VersionControlServer)tfs.GetService(typeof(VersionControlServer));
            workspaces = sourceControl.QueryWorkspaces(null, sourceControl.AuthorizedUser, System.Net.Dns.GetHostName().ToString());

            if (workspaces.Length == 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() > 0)
            {
                workspace = workspaces.Where(i => i.Name == _workspace).FirstOrDefault();
            }
            else if (workspaces.Length > 0 && workspaces.Where(i => i.Name == _workspace).Count() == 0)
            {
                workspace = sourceControl.CreateWorkspace(_workspace, sourceControl.AuthorizedUser, "DB Schema Workspace");
            }

            //get the local path and server path for the work item schema
            foreach (StoredProcedureFileConfiguration storedProcConfig in storedProcConfigurations)
            {
                sprocFileBuilder = new StringBuilder();
                localPath = storedProcConfig.LocalPath.Value;
                serverPath = storedProcConfig.TFSServerPath.Value;
                sprocsRootLocalPath = storedProcConfig.SprocsRootLocalPath;
                sprocsRootServerPath = storedProcConfig.SprocsRootServerPath;

                //check if the server path is mapped to a local path
                if (!workspace.IsServerPathMapped(serverPath))
                {
                    workspace.Map(serverPath, localPath);
                }

                if (!workspace.IsServerPathMapped(sprocsRootServerPath))
                {
                    workspace.Map(sprocsRootServerPath, sprocsRootLocalPath);
                }

                //get the latest sprocs file and get all the sprocs
                GetStatus status = workspace.Get();
                Conflict[] conflicts = workspace.QueryConflicts(new string[] { }, false);
                List<FileInfo> filesToProcess = new List<FileInfo>();
                foreach (Conflict conflict in conflicts)
                {
                    conflict.Resolution = Resolution.OverwriteLocal;
                    workspace.ResolveConflict(conflict);
                }

                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(sprocsRootLocalPath);
                ProcessDirectoryTree(sprocFileBuilder, ref line, dir);
            }
            return sprocFileBuilder;
        }
    }
}
